|
SQL Server 2017 : Use from C#
2017/10/30 |
|
This is an example to use SQL Server from C#.
|
|
| [1] | This example is based on the environment that Microsoft .NET Core 2.0 has been installed yet like here. |
|
[root@dlp ~]# dotnet new console -o MssqlTest The template "Console Application" was created successfully. Processing post-creation actions... Running 'dotnet restore' on MssqlTest/MssqlTest.csproj... Restoring packages for /home/cent/MssqlTest/MssqlTest.csproj... Generating MSBuild file /home/cent/MssqlTest/obj/MssqlTest.csproj.nuget.g.props. Generating MSBuild file /home/cent/MssqlTest/obj/MssqlTest.csproj.nuget.g.targets. Restore completed in 202.44 ms for /home/cent/MssqlTest/MssqlTest.csproj. Restore succeeded.[cent@dlp ~]$ cd MssqlTest
[cent@dlp MssqlTest]$
vi MssqlTest.csproj
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp2.0</TargetFramework>
</PropertyGroup>
# add
<ItemGroup>
<PackageReference Include="System.Data.SqlClient" Version="4.4.0" />
</ItemGroup>
</Project>
|
| [2] | Create a sample User and Database for Test. |
|
[cent@dlp ~]$ sqlcmd -S localhost -U SA Password: # create login user 1> create login cent with PASSWORD= N'password'; 2> go # create [SampleDB] 1> create database SampleDB; 2> go 1> use SampleDB; 2> go Changed database context to 'SampleDB'. # create DB user 1> create user cent for login cent; 2> go # asign DB owner role to [cent] 1> exec sp_addrolemember 'db_owner', 'cent'; 2> go # create [SampleTable] 1> create table SampleTable ( 2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50) 3> ); 4> insert into SampleTable ( 5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux' 6> ); 7> go |
| [3] | There are some basic usage to connect to SQL Server from C#. |
|
[cent@dlp ~]$ cd MssqlTest
[cent@dlp MssqlTest]$
vi Program.cs
using System;
using System.Text;
using System.Data.SqlClient;
namespace SqlServerSample
{
class Program
{
static void Main(string[] args)
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "127.0.0.1";
builder.UserID = "cent";
builder.Password = "password";
builder.InitialCatalog = "SampleDB";
Console.Write("Connecting to SQL Server... ");
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("Done.");
StringBuilder sb = new StringBuilder();
// Select from SampleTable
Console.WriteLine("Reading data from SampleTable...");
String sql = "select * from SampleTable;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(
"{0} {1} {2}",
reader.GetInt32(0),
reader.GetString(1),
reader.GetString(2)
);
}
}
}
// Insert from SampleTable
Console.Write("\r\nInserting into SampleTable...\r\n");
sb.Clear();
sb.Append("insert SampleTable (First_Name, Last_Name) ");
sb.Append("values (@first_name, @last_name);");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@first_name", "Ubuntu");
command.Parameters.AddWithValue("@last_name", "Linux");
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) inserted");
}
// Update from SampleTable
String userToUpdate = "Redhat";
Console.Write("\r\nUpdating 'Last_Name' for user " + userToUpdate + "\r\n");
sb.Clear();
sb.Append("update SampleTable set Last_Name = N'Maipo' where First_Name = @first_name");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@first_name", userToUpdate);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) updated\r\n");
}
sql = "select * from SampleTable;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(
"{0} {1} {2}",
reader.GetInt32(0),
reader.GetString(1),
reader.GetString(2)
);
}
}
}
// Delete from SampleTable
String userToDelete = "Ubuntu";
Console.Write("\r\nDeleting user '" + userToDelete + "'\r\n");
sb.Clear();
sb.Append("delete from SampleTable where First_Name = @first_name;");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@first_name", userToDelete);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) deleted");
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}
}
}
dotnet restore Restoring packages for /home/cent/MssqlTest/MssqlTest.csproj... Restore completed in 30.92 ms for /home/cent/MssqlTest/MssqlTest.csproj.[cent@dlp MssqlTest]$ dotnet run Connecting to SQL Server... Done. Reading data from SampleTable... 1 CentOS Linux 2 RedHat Linux 3 Fedora Linux Inserting into SampleTable... 1 row(s) inserted Updating 'Last_Name' for user Redhat 1 row(s) updated 1 CentOS Linux 2 RedHat Maipo 3 Fedora Linux 7 Ubuntu Linux Deleting user 'Ubuntu' 1 row(s) deleted |